﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Dapper;
using System.Linq;
using Gx.Model.Entity;//实体
using Gx.Common;//通用类
namespace Gx.Dal.Shared
{
    /// <summary>
    /// 字典配置表
    /// </summary>
    public class DictInfoDal : SqlServerHelper, IDisposable
    {
        public bool AddDictInfo(M_DictInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO M_DictInfo");
            strSql.Append("(");
            strSql.Append("DictName,");
            strSql.Append("DictType,");
            strSql.Append("DictValue,");
            strSql.Append("Status,");
            strSql.Append("Sort");
            strSql.Append(")");
            strSql.Append("VALUES");
            strSql.Append("(");
            strSql.Append("@DictName,");
            strSql.Append("@DictType,");
            strSql.Append("@DictValue,");
            strSql.Append("@Status,");
            strSql.Append("@Sort");
            strSql.Append(")");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                return conn.Execute(strSql.ToString(), model) > 0;
            }
        }

        public bool DelDictInfo(int Id)
        {
            string strSql = "UPDATE M_DictInfo SET Status=1 WHERE Id=@Id";

            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings)) { return conn.Execute(strSql.ToString(), new { Id }) > 0; }
        }

        public bool EditDictInfo(M_DictInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE M_DictInfo SET ");
            strSql.Append(" DictName=@DictName,");
            strSql.Append(" DictType=@DictType,");
            strSql.Append(" DictValue=@DictValue,");
            strSql.Append(" Status=@Status,");
            strSql.Append(" Sort=@Sort");
            strSql.Append(" WHERE Id=@Id");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings)) { return conn.Execute(strSql.ToString(), model) > 0; }
        }

        public List<M_DictInfo> GetDictInfoList(string strWhere, int pageIndex, int pageSize, out int totalCount)
        {
            totalCount = GetDictInfoCount(strWhere);
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM (");
            strSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS ORDERBYID");
            strSql.Append(",Id");
            strSql.Append(",DictName");
            strSql.Append(",DictType");
            strSql.Append(",DictValue");
            strSql.Append(",Status");
            strSql.Append(",Sort");
            strSql.AppendFormat(" FROM M_DictInfo WITH(NOLOCK) WHERE 1=1 {0}) AS T WHERE T.ORDERBYID  BETWEEN {1} AND {2}", strWhere, pageIndex, pageSize);
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                List<M_DictInfo> list = conn.Query<M_DictInfo>(strSql.ToString()).ToList();
                return list;
            }
        }

        public int GetDictInfoCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT COUNT(*) FROM M_DictInfo WITH(NOLOCK) WHERE 1=1 {0}", strWhere);
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                object obj = conn.ExecuteScalar(strSql.ToString());
                return obj.IntParse();
            };
        }

        public M_DictInfo GetM_DictInfoModel(int Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("Id");
            strSql.Append(",DictName");
            strSql.Append(",DictType");
            strSql.Append(",DictValue");
            strSql.Append(",Status");
            strSql.Append(",Sort");
            strSql.Append(" FROM M_DictInfo WITH(NOLOCK) WHERE Id=@Id");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                M_DictInfo model = conn.Query<M_DictInfo>(strSql.ToString(), new { Id }).SingleOrDefault();
                return model;
            }
        }

        public List<M_DictInfo> GetM_DictInfoList(List<int> dictValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append(" Id");
            strSql.Append(",DictName");
            strSql.Append(",DictType");
            strSql.Append(",DictValue");
            strSql.Append(",Status");
            strSql.Append(",Sort");
            strSql.AppendFormat(" FROM M_DictInfo WITH(NOLOCK) WHERE [Status]=0 AND DictType IN({0})", string.Join(",", dictValue));
            DynamicParameters p = new DynamicParameters();
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                List<M_DictInfo> list = conn.Query<M_DictInfo>(strSql.ToString(), p).ToList();
                return list;
            }
        }
    }
}
